package han.study.preparedstatement.curd;

import han.study.bean.Customer;
import han.study.bean.Order;
import han.study.utils.JDBCUtil2;
import han.study.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 使用PreparedStatement实现针对不同表的通用查询操作
 */
public class preparedStatementTest {
    public <T> List<T> getForList(Class<T> clazz, String sql, Object ...args){
        Connection conn =  null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<T> list = null;
        try{
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for(int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }
            rs = ps.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            while(rs.next()){
                T t = clazz.getDeclaredConstructor().newInstance();
                for(int i=1;i<=columnCount;i++){
                    String columnLabel = metaData.getColumnLabel(i);
                    Object columnValue = rs.getObject(i);
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }
                if(list == null) list = new ArrayList<T>();
                list.add(t);
            }
            return list;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,ps,rs);
        }
        return null;
    }
    /**
     * 返回一个实例
     * @param clazz
     * @param sql
     * @param args
     * @param <T>
     * @return T
     */
    public <T> T getInstance(Class<T> clazz, String sql, Object ...args){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            for(int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }
            rs = ps.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            if(rs.next()){
                T t = clazz.getDeclaredConstructor().newInstance();
                for(int i=1;i<=columnCount;i++){
                    Object columnValue = rs.getObject(i);
                    String columnLabel = metaData.getColumnLabel(i);
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }
                return t;
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.closeResource(conn,ps,rs);
        }
        return null;
    }

    @Test
    public void queryTest(){
        String sql = "select order_id orderId, order_name orderName,order_date orderDate from `order` where order_id = ?";
        Order order = getInstance(Order.class, sql, 1);
        System.out.println(order);
    }
    @Test
    public void testForList(){
        String sql = "select order_id orderId, order_name orderName,order_date orderDate from `order` where order_id < ?";
        List<Order> list = getForList(Order.class,sql,4);
        list.forEach(System.out::println);
    }

    // 使用dbutil进行查询曹邹
    @Test
    public void queryTest2(){
        Connection conn = null;
        try{
            conn = JDBCUtil2.getConnection();
            QueryRunner runner = new QueryRunner();
            String sql = "select id,name,email,birth from customers where id = ?";
            BeanHandler<Customer> handler = new BeanHandler<Customer>(Customer.class);
            Customer customer = runner.query(conn,sql,handler,1);
            System.out.println(customer);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtil2.closeResource(conn,null);
        }
    }
    // BeanHandler 用于类结构查询多条记录
    @Test
    public void testForList2(){
        Connection conn = null;
        try{
            conn = JDBCUtil2.getConnection();
            QueryRunner runner = new QueryRunner();
            BeanListHandler<Customer> handler = new BeanListHandler<Customer>(Customer.class);
            String sql = "select id,name,email,birth from customers";
            List<Customer> customerList = runner.query(conn, sql, handler);
            System.out.println(customerList);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtil2.closeResource(conn,null);
        }
    }

    // MapHandler 使用MAP的形式查询你多条语句
    @Test
    public void mapForList(){
        Connection conn = null;
        try{
            conn = JDBCUtil2.getConnection();
            QueryRunner runner = new QueryRunner();
            String sql = "select id,name,email,birth from customers";
            MapListHandler handler = new MapListHandler();
            List<Map<String, Object>> query = runner.query(conn, sql, handler);
            System.out.println(query);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtil2.closeResource(conn,null);
        }
    }

    // 用于查询特殊字符
    @Test
    public void testQuery(){
        Connection conn = null;
        try{
            conn = JDBCUtil2.getConnection();
            QueryRunner runner = new QueryRunner();
            String sql = "select count(*) from customers";
            ScalarHandler handler = new ScalarHandler();
            Long query = (Long)runner.query(conn, sql, handler);
            System.out.println(query);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtil2.closeResource(conn,null);
        }
    }
}
